package csc.fresher.dao.impl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import csc.fresher.dao.CustomerDao;
import csc.fresher.entity.Customer;

@Repository
public class CustomerDaoImpl implements CustomerDao, Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	@Autowired
	DataSource dataSource;

	//for test
	public Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			return DriverManager.getConnection(
					"jdbc:mysql://20.203.182.23:3306/finalProject", "root",
					"root");
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	//end test

	private static final Logger logger = Logger.getLogger(CustomerDaoImpl.class);

	@Override
	public long createCustomer(Customer customer) {
		String query = "INSERT INTO `Customer` (`isRemovable`,`IDCardNumber`,"
				+ "`FirstName`,`LastName`,`MidName`,`Address`,`Address_2`,`PhoneNumber`,"
				+ "`PhoneNumber_2`,`Email`,`Email_2`,`CreatedData`,`LastUpdate`,"
				+ "`CreatedBy`,`ApprovedBy`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setBoolean(1, customer.isRemovable());
			preparedStatement.setLong(2, customer.getIdCardNumber());
			preparedStatement.setString(3, customer.getFirstName());
			preparedStatement.setString(4, customer.getLastName());
			preparedStatement.setString(5, customer.getMidName());
			preparedStatement.setString(6, customer.getAddress());
			preparedStatement.setString(7, customer.getAddress2());
			preparedStatement.setString(8, customer.getPhoneNumber());
			preparedStatement.setString(9, customer.getPhoneNumber2());
			preparedStatement.setString(10, customer.getEmail());
			preparedStatement.setString(11, customer.getEmail2());
			preparedStatement.setTimestamp(12, customer.getCreatedDate());
			preparedStatement.setTimestamp(13, customer.getLastUpdate());
			preparedStatement.setInt(14, customer.getCreatedBy());
			preparedStatement.setInt(15, customer.getapprovedBy());
			preparedStatement.execute();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			if(rs.next()) {
				return rs.getLong(1);
			}
			
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public boolean updateCustomer(Customer customer) {
		String query = "UPDATE `Customer` "
				+ "SET `IDCardNumber`=?,`FirstName`=?,"
				+ "`LastName`=?,`MidName`=?,`Address`=?,`Address_2`=?,"
				+ "`PhoneNumber`=?,`PhoneNumber_2`=?,`Email`=?,`Email_2`=?,"
				+ "`LastUpdate`=?"
				+ "WHERE `CustomerID`=?";

		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			preparedStatement.setLong(12, customer.getCustomerID());
			preparedStatement.setLong(1, customer.getIdCardNumber());
			preparedStatement.setString(2, customer.getFirstName());
			preparedStatement.setString(3, customer.getLastName());
			preparedStatement.setString(4, customer.getMidName());
			preparedStatement.setString(5, customer.getAddress());
			preparedStatement.setString(6, customer.getAddress2());
			preparedStatement.setString(7, customer.getPhoneNumber());
			preparedStatement.setString(8, customer.getPhoneNumber2());
			preparedStatement.setString(9, customer.getEmail());
			preparedStatement.setString(10, customer.getEmail2());
			preparedStatement.setTimestamp(11, customer.getLastUpdate());
			preparedStatement.execute();
			return true;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return false;
	}

	@Override
	public void deleteCustomer(Customer customer) {
		String query = "DELETE FROM `Customer` WHERE `CustomerID`=?";

		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			preparedStatement.setLong(1, customer.getCustomerID());
			preparedStatement.execute();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
	}

	@Override
	public Customer getCustomerByID(long customerID) {
		String query = "SELECT * FROM `Customer` WHERE `CustomerID`=?";
		Customer customer = new Customer();
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			preparedStatement.setLong(1, customerID);

			ResultSet rs = preparedStatement.executeQuery();

			customer = getCustomer(rs);
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return customer;
	}

	@Override
	public List<Customer> getCustomerByCardNumberID(long cardNumberID) {
		List<Customer> listCustomer = new ArrayList<Customer>();
		String query = "SELECT * FROM `Customer` WHERE `IDCardNumber`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			preparedStatement.setLong(1, cardNumberID);

			ResultSet rs = preparedStatement.executeQuery();

			while(rs.next()){
				Customer customer = new Customer();
				customer.setCustomerID(rs.getLong("CustomerID"));
				customer.setRemovable(rs.getBoolean("isRemovable"));
				customer.setIdCardNumber(rs.getLong("IDCardNumber"));
				customer.setFirstName(rs.getString("FirstName"));
				customer.setLastName(rs.getString("LastName"));
				customer.setMidName(rs.getString("MidName"));
				customer.setAddress(rs.getString("Address"));
				customer.setAddress2(rs.getString("Address_2"));
				customer.setPhoneNumber(rs.getString("PhoneNumber"));
				customer.setPhoneNumber2(rs.getString("PhoneNumber_2"));
				customer.setEmail(rs.getString("Email"));
				customer.setEmail2(rs.getString("Email_2"));
				customer.setCreatedDate(rs.getTimestamp("CreatedData"));
				customer.setLastUpdate(rs.getTimestamp("LastUpdate"));
				customer.setCreatedBy(rs.getInt("CreatedBy"));
				customer.setApprovedBy(rs.getInt("ApprovedBy"));
				listCustomer.add(customer);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return listCustomer;
	}

	@Override
	public List<Customer> getCustomerByFirstName(String name) {
		List<Customer> resultList = new ArrayList<Customer>();
		String query = "SELECT * FROM `Customer` WHERE `FirstName` LIKE '%"
				+ name + "%'";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);

			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				Customer customer = new Customer();
				customer.setCustomerID(rs.getLong("CustomerID"));
				customer.setRemovable(rs.getBoolean("isRemovable"));
				customer.setIdCardNumber(rs.getLong("IDCardNumber"));
				customer.setFirstName(rs.getString("FirstName"));
				customer.setLastName(rs.getString("LastName"));
				customer.setMidName(rs.getString("MidName"));
				customer.setAddress(rs.getString("Address"));
				customer.setAddress2(rs.getString("Address_2"));
				customer.setPhoneNumber(rs.getString("PhoneNumber"));
				customer.setPhoneNumber2(rs.getString("PhoneNumber_2"));
				customer.setEmail(rs.getString("Email"));
				customer.setEmail2(rs.getString("Email_2"));
				customer.setCreatedDate(rs.getTimestamp("CreatedData"));
				customer.setLastUpdate(rs.getTimestamp("LastUpdate"));
				customer.setCreatedBy(rs.getInt("CreatedBy"));
				customer.setApprovedBy(rs.getInt("ApprovedBy"));
				resultList.add(customer);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	public Customer getCustomer(ResultSet rs) throws SQLException {
		Customer customer = new Customer();
		if (rs.next()) {
			customer.setCustomerID(rs.getLong("CustomerID"));
			customer.setRemovable(rs.getBoolean("isRemovable"));
			customer.setIdCardNumber(rs.getLong("IDCardNumber"));
			customer.setFirstName(rs.getString("FirstName"));
			customer.setLastName(rs.getString("LastName"));
			customer.setMidName(rs.getString("MidName"));
			customer.setAddress(rs.getString("Address"));
			customer.setAddress2(rs.getString("Address_2"));
			customer.setPhoneNumber(rs.getString("PhoneNumber"));
			customer.setPhoneNumber2(rs.getString("PhoneNumber_2"));
			customer.setEmail(rs.getString("Email"));
			customer.setEmail2(rs.getString("Email_2"));
			customer.setCreatedDate(rs.getTimestamp("CreatedData"));
			customer.setLastUpdate(rs.getTimestamp("LastUpdate"));
			customer.setCreatedBy(rs
					.getInt("CreatedBy"));
			customer.setApprovedBy(rs
					.getInt("ApprovedBy"));
		}
		return customer;
	}

	@Override
	public void markCustomerAsRemovable(Customer customer) {
		String query = "UPDATE `Customer` "
				+ "SET `isRemovable`=1 "
				+ "WHERE `CustomerID`=?";

		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			preparedStatement.setLong(1, customer.getCustomerID());
			preparedStatement.execute();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
	}

	//for test
	
//	public static void main(String[] agrs) {
//		// Customer customer = new Customer(false, "555555", "Nghia", "Phan",
//		// "The", "ss", "ss2", "e", "e2", "090", "0902", new Timestamp(
//		// System.currentTimeMillis()), new Timestamp(
//		// System.currentTimeMillis()), 30000, 30000);
//		CustomerDao customerDao = new CustomerDaoImpl();
//		List<Customer> resultList = customerDao.getCustomerByFirstName("p");
//
//		for (int i = 0; i < resultList.size(); i++) {
//			Customer customer = new Customer();
//			customer = resultList.get(i);
//			System.out.println(customer.getAddress());
//		}
//
//		// Customer customer = new Customer(123456789009L,false, "111111",
//		// "Nghia", "Phan",
//		// "The", "ss", "ss2", "e", "e2", "090", "0902", new Date(
//		// System.currentTimeMillis()), new Date(
//		// System.currentTimeMillis()), 30000, 30000);
//
//		// Customer customer = new Customer();
//		//
//		// CustomerDao customerDao = new CustomerDaoImpl();
//		// customer = customerDao.getCustomerByFirstName(name)(555555);
//		//
//		// System.out.println(customer.getAddress());
//	}

}
